Allowing non-superuser to access Apache AGE through python driver

[CC-Hsu]

Hi, Team,

Previously we try to use Apache AGE Python driver to access an AGE database.

But we find that we can not run the driver successfully without a DB user with superuser privilege due to LOAD age; command.

We know that in the prerequisite information one need to test the setup with LOAD age; command in the psql. https://github.com/apache/age/tree/master/drivers/python#check-age-loaded-on-your-postgresql

And we also see the driver also execute LOAD age; first (I copy the corresponding code snippet below). https://github.com/apache/age/blob/master/drivers/python/age/age.py#L32

def setUpAge(conn:ext.connection, graphName:str):
    with conn.cursor() as cursor:
        cursor.execute("LOAD 'age';")

Although the LOAD command is superuser-restricted, it may be replaced by setting local_preload_libraries GUC, so that LOAD command can be skipped and allow non-superuser DB roles to use Apache AGE objects.

Is it possible to improve the python driver to also support non-superuser usage in this way?

Best Regards.

[aru-d-at]

Hi Hsu,

Thank you for pointing out this limitation. It seems like non-superuser connections are not possible with the current drivers. This may cause an issue in deploying production-ready applications in the future.

For now, I will look into your suggestion first.

Regards

[jrgemignani]

@CC-Hsu

Although the LOAD command is superuser-restricted, it may be replaced by setting local_preload_libraries GUC, so that LOAD command can be skipped and allow non-superuser DB roles to use Apache AGE objects.

From the link for load -

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ — the specified filename must begin with exactly that string. (It is the database administrator's responsibility to ensure that only “safe” libraries are installed there.)

We do not install AGE there by default for security reasons as this is the job of the DBA for that database.

[aru-d-at]

@jrgemignani Noted. Thank you!

Do you think it would be within the best interest to have the function modified with an argument to connect for non-superuser? Of course they would require sufficient privileges as below (possibly few more than listed).

@CC-Hsu So, you'll have to grant privilege as Superuser to the required schemas/tables for the non-superuser:

GRANT USAGE ON SCHEMA ag_catalog TO tester;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ag_catalog TO tester;

GRANT USAGE ON SCHEMA test TO tester;
GRANT ALL PRIVILEGES ON SCHEMA test TO tester;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA test TO tester;
GRANT ALL PRIVILEGES ON TABLE test._ag_label_vertex TO tester;

Then, modify your Queries like so:

SELECT * FROM ag_catalog.cypher('test', $$
MATCH (v)
RETURN v
$$) as (v ag_catalog.agtype);

Be aware there will be a failure for the first query. However, subsequent queries will be work. Unfortunately, the fix for this would require you to run the LOAD '$libdir/plugins/age.so' as the non-superuser..

If the modified LOAD command were run, then the query would not require a modification.

With the above privileges the non-superuser will not be able to create_graph, create_vlabel & create_elabel.

[jrgemignani]

Be aware there will be a failure for the first query. However, subsequent queries will be work. https://github.com/apache/age/issues/41.

I need to point out that, per that link, you can use load without superuser access as I stated above. If you don't want to use the load extension command, you can always just expect the first cypher command to fail. Additionally, if you set the search_path as shown in that link, you don't need to prefix anything with ag_catalog.

Do you think it would be within the best interest to have the function modified with an argument to connect for non-superuser?

Modifying the cypher function(s) wouldn't change how the extension is loaded by PostgreSQL, as far as I know. PostgreSQL and DBAs control the access to components of the database.

[CC-Hsu]

Hi, All,

Thanks for your attention. 😊

Regarding to LOAD command issue, I think besides of changing the shared object location to $libdir/plugins/, another option may be setting local_preload_libraries.

-- Executed by superuser for one time only
ALTER SYSTEM SET local_preload_libraries = '$libdir/age';
SELECT pg_reload_conf();

As far as I know, this can make AGE loaded for non-superuser roles without changing age.so file location.

[jrgemignani]

@CC-Hsu Per https://www.postgresql.org/docs/current/sql-load.html

Non-superusers can only apply LOAD to library files located in $libdir/plugins/ — the specified filename must begin with exactly that string. (It is the database administrator's responsibility to ensure that only “safe” libraries are installed there.)

From your link -

This option can be set by any user. Because of that, the libraries that can be loaded are restricted to those appearing in the plugins subdirectory of the installation's standard library directory. (It is the database administrator's responsibility to ensure that only safe libraries are installed there.) Entries in local_preload_libraries can specify this directory explicitly, for example $libdir/plugins/mylib, or just specify the library name — mylib would have the same effect as $libdir/plugins/mylib.

Using local_preload_libraries requires the libraries to be installed in the plugin directory by the dba. Meaning, that you could just use load.

[CC-Hsu]

Hi, jrgemignani,

Thanks for point out this point 😅; there are too many similar GUCs for me.

What about session_preload_libraries as mentioned at the end of local_preload_libraries page?

It seems that this setting does not require the loaded plugin to be placed in $libdir/plugins/.

[jrgemignani]

@CC-Hsu These are all DBA commands for database administrators of said database to set up. AGE is an extension/plugin to PostgreSQL and purposely makes no changes, as that is not our role, to access AGE. How a DBA decides to allow access to AGE is up to that DBA and their organizations guidelines, not us.

[CC-Hsu]

Hi, jrgemignani,

I think most DBAs may willing to prepare these DBA-commands at startup for applications-requirement rather than allowing applications running in superuser privilege.

So I guess setting these parameters can be a better way to replace LOAD command requirement and no need to change too much the current Apache AGE code for this issue.

[jrgemignani]

@CC-Hsu

I think most DBAs may willing to

That is why this is up to the DBAs - most is not all nor is it a good business practice for us to do this for a DBA. We can point users to how to make these changes. However, we can't add them to the installation of AGE.

[CC-Hsu]

HI, jrgemignani,

Understand.

I just try to find out some possible suggestion to make this python module run without superuser privilege based on my limited knowledge.

It is up to the Apache AGE community to decide the best way to achieve this goal.

Best Regards.

[opentyler]

This is still an issue that prevents python users from using apache age. Not being able to use Apache AGE as a non-superuser is a fairly big concern.

A simple fix would be to update the python driver as @aru-d-at recommended, which is add an argument to connect as non-superuser. This seems like a driver-issue.

def setUpAge(conn:ext.connection, graphName:str, superuser:bool=True):
    with conn.cursor() as cursor:
        if superuser:
            cursor.execute("LOAD 'age';")
        else:
            cursor.execute("LOAD '$libdir/plugins/age';")

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[jrgemignani]

@opentyler If someone would like to update the python driver, that would be great. This is an open source project and unfortunately, not all of us know python or have hotter items that need to be resolved.