Return to Answer

Your Question and comment confuse several issues: PostgreSQL has logins (technically, login roles¹) which are used to connect to an instance (host+port). The sde login is used to manage the geodatabase. sde should never own spatial data, much the way you should not use the postgres login for anything but administering the instance.

Logins map to schemas, which are, in effect, users in the database³. To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The CreateDatabaseYser tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

Your Question and comment confuse several issues. PostgreSQL has logins (technically, login roles¹) which are used to connect to an instance (host+port). The sde login is used to manage the geodatabase. sde should never own spatial data, much the way you should not use the postgres login for anything but administering the instance.

Logins map to schemas, which are, in effect, users in the database³. To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The Create Database User tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

PG instances also have databases. You should always create a new database for spatial data (do not use postgres or any template database). The database name for a geodatabase cannot be "ecofield.sde" (forbidden "." character in name -- all names must be SQL standard compliant AND must be lowercase). You may be confusing the database name with the connection file (which is a binary file with a .sde suffix).

Logins map to schemas, which are, in effect, users in the database². To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The CreateDatabaseYser tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

² Schemas are used to reference objects within a database. They're another source of complexity, because roles "own" tables, which are referenced by schema, and schemas are authorized by roles, and can have USAGE granted to other roles.

PG instances also have databases. You should always create a new database for spatial data (do not use postgres or any template database). The database name for a geodatabase cannot be "ecofield.sde" (forbidden "." character in name -- all names must be SQL standard compliant² AND must be lowercase). You may be confusing the database name with the connection file (which is a binary file with a .sde suffix).

Logins map to schemas, which are, in effect, users in the database³. To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The CreateDatabaseYser tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

² ISO SQL Standard names are those that start with an alpha (formerly "Latin a-z", but now UTF letter codepoints), and have zero, one or more alphanumeric (+ 0-9) or underscore (_) characters (they also cannot be reserved names -- You can't name your table "select"). ISO names are case-independent, which are mapped to lower-case in PostgreSQL (they're UPPERCASE in Oracle). Most RDBMS vendors, PostgreSQL included, allow for non-standard naming (including MiXedCase, punctuation, and spaces), but to do so, the name must be surrounded by quotes (") each and every time it is referenced (e.g., "ecofield.sde"). The pain of having to explicitly format every single non-compliant name is significant, and spread among any who use your database (who won't be ArcGIS users, because it does not honor quotes, and the names will not be found, and therefore unusable).

³ Schemas are used to reference objects within a database. They're another source of complexity, because roles "own" tables, which are referenced by schema, and schemas are authorized by roles, and can have USAGE granted to other roles.

Your Question and comment confuse several issues: PostgreSQL has logins (technically, login roles¹) which are used to connect to an instance (host+port). The sde login is used to manage the geodatabase. sde should never own spatial data, much the way you should not use the postgres login for anything but administering the instance.

PG instances also have databases. You should always create a new database for spatial data (do not use postgres or any template database). The database name for a geodatabase cannot be "ecofield.sde" (forbidden "." character in name -- all names must be SQL standard compliant AND must be lowercase). You may be confusing the database name with the connection file (which is a binary file with a .sde suffix).

Logins map to schemas, which are, in effect, users in the database². To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The CreateDatabaseYser tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

You cannot use the sde login to register any tables not owned by sde (which, to be clear, should not exist). So, while the sde user may own the database, it is not the "data owner" (that's what the login/schema pairs are for).

In order to register data owned by schema shape_test with ArcGIS, you must have a login named shape_test (and I think it's required to be password-protected; it certainly should be), which is the authorization to schema shape_test, and a connection file which connects to database ecofield in instance localhost:5432. Then you can connect as this user, and right-click Register the table.

¹ There are also group roles (aka "groups") which are... complex, because they can own objects, in addition to being used to GRANT permissions, but I'm going to avoid that rabbit hole now.

² Schemas are used to reference objects. They're another source of complexity, because roles "own" tables, which are referenced by schema, and schemas are authorized by roles, and can have USAGE granted to other roles.

Your Question and comment confuse several issues: PostgreSQL has logins (technically, login roles¹) which are used to connect to an instance (host+port). The sde login is used to manage the geodatabase. sde should never own spatial data, much the way you should not use the postgres login for anything but administering the instance.

PG instances also have databases. You should always create a new database for spatial data (do not use postgres or any template database). The database name for a geodatabase cannot be "ecofield.sde" (forbidden "." character in name -- all names must be SQL standard compliant AND must be lowercase). You may be confusing the database name with the connection file (which is a binary file with a .sde suffix).

Logins map to schemas, which are, in effect, users in the database². To eliminate the ambiguity, ArcGIS requires that any login which will create data through ArcGIS tools must have the same schema name as login name (and both must be lowercase). The CreateDatabaseYser tool can be used to generate login/schema pairs in the way that ArcGIS expects, but it isn't required.

You cannot use the sde login to register any tables not owned by sde (which, to be clear, should not exist). So, while the sde user may own the database, it is not the "data owner" (that's what the login/schema pairs are for).

In order to register data owned by schema shape_test with ArcGIS, you must have a login named shape_test (and I think it's required to be password-protected; it certainly should be), which is the authorization to schema shape_test, and a connection file which connects to database ecofield in instance localhost:5432. Then you can connect as this user, and right-click Register the table.

¹ There are also group roles (aka "groups") which are... complex, because they can own objects, in addition to being used to GRANT permissions, but I'm going to avoid that rabbit hole now.

² Schemas are used to reference objects within a database. They're another source of complexity, because roles "own" tables, which are referenced by schema, and schemas are authorized by roles, and can have USAGE granted to other roles.